package db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import beans.Person;
import beans.PersonAssignment;
import beans.Skill;

public class PersonDAO {

	public static Person getPerson(String email) {
		try {
			Connection c = DataUtils.getDBConnection();
		
			PreparedStatement st = c.prepareStatement(SQL.map.get("SPERSON.sql").toString());
			st.setString(1, email);
			
			ResultSet rs = st.executeQuery();
			
		    rs.next();
		    
		    int pid = rs.getInt("PID"); 
		    String subType = rs.getString("SUB_TYP");

		    rs.close();
		    st.close();
		    
		    return new Person(pid, email, Person.subTypeToTypeId(subType));
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	public static List getPeopleAssignedToProject(int projectId) {
		try {
			Connection c = DataUtils.getDBConnection();
			
			PreparedStatement st = c.prepareStatement(SQL.map.get("SPERSONASSIGNMENT.sql").toString());
			st.setInt(1, projectId);
			
			ResultSet rs = st.executeQuery();
		    
		    List assignments = new ArrayList();
		    
		    while (rs.next()) {
		    
		    	int pid = rs.getInt("PID"); 
		    	String subType = rs.getString("SUB_TYP");
		    	String email   = rs.getString("EMAIL");
		    	Calendar startDate = Calendar.getInstance(); startDate.setTime(rs.getDate("START_DATE"));
		    	Calendar endDate = Calendar.getInstance(); endDate.setTime(rs.getDate("END_DATE"));
		    	int  percent = rs.getInt("PERCENT");
		    	
		    	assignments.add(new PersonAssignment(new Person(pid, email, Person.subTypeToTypeId(subType)),startDate,endDate,percent));
		    }
		    
		    rs.close();
		    st.close();
		    
		    return assignments;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	public static List getSkills(Person person) {
		try {
			Connection c = DataUtils.getDBConnection();
			
			PreparedStatement st = c.prepareStatement(SQL.map.get("SPERSONSKILLS.sql").toString());
			st.setInt(1, person.id);
			st.setInt(2, person.id);
			
			ResultSet rs = st.executeQuery();
		    
		    List skills = new ArrayList();
		    
		    while (rs.next()) {
		    
		    	int skillId = rs.getInt("SKILL_ID");
		    	int hasLevel = rs.getInt("HAS_LEVL");
		    	int needsLevel = rs.getInt("NEEDS_LEVL");
		    	String description    = rs.getString("DESCRIPTION");
		    	
		    	skills.add(new Skill(skillId, description, hasLevel, needsLevel));
		    }
		    
		    rs.close();
		    st.close();
		    
		    return skills;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	public static List getPeople() {
		try {
			Connection c = DataUtils.getDBConnection();
			
			PreparedStatement st = c.prepareStatement(SQL.map.get("SPEOPLE.sql").toString());
			
			ResultSet rs = st.executeQuery();
		    
		    List people = new ArrayList();
		    
		    while (rs.next()) {
		    
		    	
		    	people.add(new Person(rs.getInt("PID"), rs.getString("EMAIL"), Person.subTypeToTypeId(rs.getString("SUB_TYP"))));
		    }
		    
		    rs.close();
		    st.close();
		    
		    return people;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	public static void addUser(String email, String role) {
		try {
			Connection c = DataUtils.getDBConnection();
		
			PreparedStatement st = c.prepareStatement(SQL.map.get("IPERSON.sql").toString());
			st.setString(1, email);
			st.setString(2, Person.roleToSubType(role));
			
			st.executeUpdate();
			
		    st.close();
		    
		    return;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	public static Person getPerson(int person_id) {
		try {
			Connection c = DataUtils.getDBConnection();
		
			PreparedStatement st = c.prepareStatement(SQL.map.get("SPERSON2.sql").toString());
			st.setInt(1, person_id);
			
			ResultSet rs = st.executeQuery();
			
		    rs.next();
		    
		    String email = rs.getString("EMAIL"); 
		    String subType = rs.getString("SUB_TYP");

		    rs.close();
		    st.close();
		    
		    return new Person(person_id, email, Person.subTypeToTypeId(subType));
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	public static void assignPersonToProject(int personId, int projId,
			String sd, String ed, int p) {
		try {
			Connection c = DataUtils.getDBConnection();
		
			PreparedStatement st = c.prepareStatement(SQL.map.get("IPERSONPROJECT.sql").toString());
			st.setInt(1, projId);
			st.setInt(2, personId);
			st.setDate(3, Date.valueOf(sd));
			st.setDate(4, Date.valueOf(ed));
			st.setInt(5, p);
			st.executeUpdate();
			
		    st.close();
		    
		    return;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

}
